package controllers;

import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.CASE_GOODS_LEND;
import model.CaseGoods;
import model.CaseGridInfo;
import model.GoodsHistory;
import model.T_RelationGridGoods;
import model.User_list;

import com.et.ar.exception.ActiveRecordException;
import com.et.ar.exception.TransactionException;
import com.et.mvc.JsonView;
import com.wzzz.util.SysDate;

/**
 * 
 * CasePropertyLendController.java
 * @author crab
 *
 * Remark:物品借出Controller
 */
public class CasePropertyLendController extends ApplicationController {
	private static final String enter = " \n";
	public JsonView getStoredGoods(int rows, int page) throws ActiveRecordException{
		User_list ul = (User_list)session.getAttribute("sessionAccount");
		String company_id = ul.getUser_company();
		StringBuilder condition = new StringBuilder();
		condition.append("1=1 "); 
		condition.append(" and operate_company=").append(company_id).append(enter); 
		String ajmc = request.getParameter("AJMC");//案件名称
		if(ajmc!=null && ajmc.length()>0)
			buildCondition(condition,"%"+ajmc+"%","TCGI.CASE_NAME","like");
		String wpmc = request.getParameter("WPMC");//物品名称
		if(wpmc!=null && wpmc.length()>0)
			buildCondition(condition,"%"+wpmc+"%","TCGI.GOODS_NAME","like");
		String badw = request.getParameter("BADW");//办案单位
		if(badw!=null && badw.length()>0)
			buildCondition(condition,"%"+badw+"%","TCGI.CASE_COMPANY","like");
		String bar = request.getParameter("BAR");//办案人员
		if(bar!=null && bar.length()>0)
			buildCondition(condition,"%"+bar+"%","TCGI.CASE_MAN","like");
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT TRGG.STATUS,").append(enter);
		sql.append("       TCGI.GOODS_NAME,").append(enter);
		sql.append("       TCGI.CASE_NAME,").append(enter);
		sql.append("       TCGI.CASE_NUM,").append(enter);
		sql.append("       TRGG.CREATE_DATE,").append(enter);
		sql.append("       TCRI.ROOM_NAME,").append(enter);
		sql.append("       TCSI.SHELF_NAME,").append(enter);
		sql.append("       TCGRI.GRID_NUM,").append(enter);
		sql.append("       TRGG.T_CODE").append(enter); 
		sql.append("  FROM T_RELATION_GRID_GOODS TRGG").append(enter);
		sql.append(" INNER JOIN T_CASE_GOODS_INFO TCGI ON TRGG.T_CODE = TCGI.GOODS_TCODE  AND NVL(TCGI.STATUS, 0) = 0").append(enter);
		sql.append(" INNER JOIN T_CASE_ROOM_INFO TCRI ON TRGG.CASE_ROOM_ID = TCRI.ROOM_ID").append(enter);
		sql.append(" INNER JOIN T_CASE_SHELF_INFO TCSI ON TRGG.CASE_SHELF_ID = TCSI.SHELF_ID").append(enter);
		sql.append(" INNER JOIN T_CASE_GRID_INFO TCGRI ON TRGG.CASE_GRID_ID = TCGRI.GRID_ID").append(enter);
		sql.append(" where ").append(condition.toString());
		sql.append("ORDER BY case_num");
		StringBuilder count_sql = new StringBuilder();
		count_sql.append("select count(*) count").append(enter);
		count_sql.append("  FROM T_RELATION_GRID_GOODS TRGG").append(enter);
		count_sql.append(" INNER JOIN T_CASE_GOODS_INFO TCGI ON TRGG.T_CODE = TCGI.GOODS_TCODE  AND NVL(TCGI.STATUS, 0) = 0").append(enter);
		count_sql.append(" INNER JOIN T_CASE_ROOM_INFO TCRI ON TRGG.CASE_ROOM_ID = TCRI.ROOM_ID").append(enter);
		count_sql.append(" INNER JOIN T_CASE_SHELF_INFO TCSI ON TRGG.CASE_SHELF_ID = TCSI.SHELF_ID").append(enter);
		count_sql.append(" INNER JOIN T_CASE_GRID_INFO TCGRI ON TRGG.CASE_GRID_ID = TCGRI.GRID_ID").append(enter);
		count_sql.append(" where ").append(condition.toString());
		List<Map<String,Object>> count_map = T_RelationGridGoods.getResultMap(T_RelationGridGoods.class,count_sql.toString(),null, "",0,Integer.MAX_VALUE);
		Long total = count_map.size()>0?Long.parseLong(count_map.get(0).get("count").toString()):1;
		List<Map<String,Object>> shelfGoodslist = T_RelationGridGoods.getResultMap(T_RelationGridGoods.class,sql.toString(),null, "",rows, (page - 1) * rows);
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("total", total);//数据总数
		result.put("rows", shelfGoodslist);
		return new JsonView(result);
	}
	/**
	 * 构造查询条件得方法
	 * @param cond 查询条件，第一次是 1=1 ，然后不停得拼接 and条件
	 * @param value 查询得值，如果是like查询，需要手动加'%' %value%
	 * @param target 目标字段
	 * @param operate 操作符
	 */
	private void buildCondition(StringBuilder cond,String value,String target,String operate){
		if(value!=null && value.length()>0){
			cond.append(" and ").append(target).append(" ").append(operate).append(" '").append(value).append("'");
		}
	}
	/**
	 * 物品调出的方法
	 * @return
	 * @throws UnsupportedEncodingException 
	 * @throws ActiveRecordException 
	 */
	public JsonView doUnStoredGoods() throws UnsupportedEncodingException{
		User_list ul = (User_list)session.getAttribute("sessionAccount");
		request.setCharacterEncoding("UTF-8");
		String selectTcode = request.getParameter("selectTcode");
		String lend_reason = request.getParameter("lend_reason");
		lend_reason = new String(lend_reason.getBytes("iso-8859-1"),"utf-8");
		String lend_limit = request.getParameter("lend_limit")==null?"0":request.getParameter("lend_limit");
		String lend_company = request.getParameter("lend_company");
		lend_company = new String(lend_company.getBytes("iso-8859-1"),"utf-8");
		String lend_person = request.getParameter("lend_person");
		lend_person = new String(lend_person.getBytes("iso-8859-1"),"utf-8");
		try {
			
			//获取条形码对应的物品ID
			List<Map<String,Object>> selectGoodsIds = getGoodsIdByTcode(selectTcode);
			//获得条码对应格子中存有袋子的数量
			List<Map<String,Object>> m = getCountGrid(selectTcode);
			if(selectGoodsIds.size()>0){
				T_RelationGridGoods.beginTransaction();
				//删除物品格子关联表得数据 T_RELATION_GRID_GOODS
				T_RelationGridGoods.deleteAll(T_RelationGridGoods.class, "T_CODE='"+selectTcode+"'", null);
				if(m.size()==1){//如果等于1，说明只有格子下只有一个物品，所以将格子的使用状态设置成0
					String case_grid_id = m.get(0).get("case_grid_id").toString();
					StringBuilder ugrid = new StringBuilder();
					ugrid.append(" grid_status=0 ");
					String ugridCondition = " grid_id="+case_grid_id;
					CaseGridInfo.updateAll(CaseGridInfo.class, ugrid.toString(), null, ugridCondition, null);
				}
				//修改物品状态
				StringBuilder updates = new StringBuilder();
				updates.append(" keep_status=3 ");
				StringBuilder updateconditions = new StringBuilder();
				updateconditions.append(" goods_tcode='").append(selectTcode).append("' and NVL(STATUS, 0) = 0");
				CaseGoods.updateAll(CaseGoods.class, updates.toString(), null, updateconditions.toString(), null);
				//新增借出表
				for(int i=0;i<selectGoodsIds.size();i++){
					CASE_GOODS_LEND po = new CASE_GOODS_LEND();
					po.setLEND_COMPANY(lend_company);
					po.setLEND_LIMIT(lend_limit);
					po.setLEND_PERSON(lend_person);
					po.setLEND_DATE(SysDate.getToday());
					
					po.setLEND_REASON(lend_reason);
					po.setGOODS_ID(selectGoodsIds.get(i).get("goods_id").toString()); 
					po.setT_CODE(selectTcode);
					po.setOPERATE_COMPANY(ul.getUser_company());
					po.setOPERATOR(String.valueOf(ul.getUser_id()));
					po.save();
					//历史信息
					GoodsHistory history = new GoodsHistory();
					history.setCASE_GOODS_ID(selectGoodsIds.get(i).get("goods_id").toString());  //物品ID
					history.setCASE_GOODS_TCODE(selectTcode);      //物品条形码
//					因为是出库（借出），所以不记录保管室和保管柜等信息
//					history.setCASE_ROOM_ID(room_id);         //保管室
//					history.setSHELF_ID(shelf_id);            //保管柜
//					history.setGRID_ID(gridID);               //保管柜格子ID
					history.setLEND_COMPANY(lend_company);
					history.setLEND_PERSON(lend_person);
					history.setLEND_DATE(SysDate.getToday());
					
					history.setGOODS_STATUS("已借出");         //物品状态
					history.setHISTORY_COMPANY(ul.getUser_company());   //轨迹表操作单位
					history.setHISTORY_PERSON(String.valueOf(ul.getUser_id()));  //轨迹表操作人
					history.setHISTORY_DATE(SysDate.getToday());    //轨迹表操作时间
					history.setSTATUS("1");                   //财物处理操作标识  1借出
					history.save();
				}
			}
			T_RelationGridGoods.commit();
			JsonView view = new JsonView("success:true");
			view.setContentType("text/html;charset=utf-8");
			return view;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				T_RelationGridGoods.rollback();
			} catch (TransactionException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				JsonView view = new JsonView("success:false");
				view.setContentType("text/html;charset=utf-8");
				return view;
			}
			JsonView view = new JsonView("success:false");
			view.setContentType("text/html;charset=utf-8");
			return view;
		}
	}
	private List<Map<String,Object>> getGoodsIdByTcode(String selectTcode) throws ActiveRecordException{
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT TCGI.GOODS_ID").append(enter);
		sql.append("  FROM T_RELATION_GRID_GOODS TRGG").append(enter);
		sql.append(" INNER JOIN T_CASE_GOODS_INFO TCGI ON TRGG.T_CODE = TCGI.GOODS_TCODE").append(enter);
		sql.append("                                  AND NVL(TCGI.STATUS, 0) = 0").append(enter);
		sql.append("                                  AND  TCGI.GOODS_TCODE = '").append(selectTcode).append("'").append(enter);
		return T_RelationGridGoods.getResultMap(T_RelationGridGoods.class, sql.toString(),null, "",0,Integer.MAX_VALUE);
	}
	private List<Map<String,Object>> getCountGrid(String selectTcode) throws ActiveRecordException{
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT t_code,trgg.case_grid_id ").append(enter);
		sql.append("  FROM T_RELATION_GRID_GOODS TRGG").append(enter);
		sql.append(" INNER JOIN (SELECT CASE_GRID_ID").append(enter);
		sql.append("               FROM T_RELATION_GRID_GOODS").append(enter);
		sql.append("              WHERE T_CODE = '").append(selectTcode).append("') TMP ON TRGG.CASE_GRID_ID =").append(enter);
		sql.append("                                         TMP.CASE_GRID_ID").append(enter);
		return T_RelationGridGoods.getResultMap(T_RelationGridGoods.class, sql.toString(),null, "",0,Integer.MAX_VALUE);
	}
}
